# -*-coding:utf8-*-
__author__ = "no name"

import requests
import json
import random
import pymysql
import sys
import datetime
import time
import re
from bs4 import BeautifulSoup
#from imp import reload
from multiprocessing.dummy import Pool as ThreadPool


def datetime_to_timestamp_in_milliseconds(d):
    def current_milli_time(): return int(round(time.time() * 1000))

    return current_milli_time()


#reload(sys)


def LoadUserAgents(uafile):
    uas = []
    with open(uafile, 'rb') as uaf:
        for ua in uaf.readlines():
            if ua:
                uas.append(ua.strip()[:-1])
    random.shuffle(uas)
    return uas

head = {
    'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_1) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/75.0.3753.4 Safari/537.36',
}

time1 = time.time()

#url = "https://price.pcauto.com.cn/sg22783/config.html"

conn = pymysql.connect(host='localhost', user='root', passwd='admin9527', db='python', charset='utf8')

carArr = []
#查询所有的汽车
def inquireCar():
    sqlStr = "SELECT id,name,href_id FROM pcauto_car as a WHERE id not in (select car_id from pcauto_car_relation group by car_id ) AND id!=2841"
    try:
        # Please write your MySQL's information.
        carCur = conn.cursor()
        carCur.execute(sqlStr)
        conn.commit()
        for temp in carCur.fetchall():
            carArr.append(temp)
    except Exception as e:
        print("inquire car error: " + e)
    #print(str(carArr))

paramTypeArr = []
#查询参数类型
def inquireParamType():
    sqlStr = "SELECT a.id,a.parent_id,a.field,a.name,b.name as parent_name FROM pcauto_car_param_type as a,pcauto_car_param_type as b WHERE a.parent_id != 0 AND a.parent_id = b.id "
    try:
        # Please write your MySQL's information.
        cur = conn.cursor()
        cur.execute(sqlStr)
        conn.commit()
        for temp in cur.fetchall():
            paramTypeArr.append(temp)
    except Exception as e:
        print("inquire car type error: " + e)

#筛选参数名及对应的数据
fieldArr =[]
valueArr = []
def filterFieldName(tempDict,carID):
    valueCount = len(tempDict["body"]["items"][0]["ModelExcessIds"])
    fieldArr.clear()
    fieldArr.append("car_id")

    valueArr.clear()
    for index in range(valueCount):
        valueArr.append([carID])
    #
    for typeItem in paramTypeArr :
        for valueItem in tempDict["body"]["items"]:
            if typeItem[-2] == "参配看图":
                if typeItem[-1] == valueItem["Item"]:
                    fieldArr.append("param."+str(typeItem[2]))
                    for i in range(len(valueItem["ModelExcessIds"])):
                        #print("i:" + str(i))
                        valueArr[i].append('\"'+str(valueItem["ModelExcessIds"][i]["Value"])+'\"')
                    break
            elif typeItem[-2] == valueItem["Name"]:
                fieldArr.append("param."+str(typeItem[2]))
                for i in range(len(valueItem["ModelExcessIds"])):
                    valueArr[i].append('\"'+str(valueItem["ModelExcessIds"][i]["Value"])+'\"')
                break
    #print("fieldArr---------" + str(fieldArr))
    #print("valueArr---------" + str(valueArr))

#筛选参数名及对应的数据-option
optionFieldArr =[]
optionValueArr = []
def filterOptionFieldName(optionTempDict,carID):
    if len(optionTempDict["body"]["items"]) == 0 : return
    valueCount = len(optionTempDict["body"]["items"][0]["ModelExcessIds"])
    optionFieldArr.clear()
    optionFieldArr.append("car_id")

    optionValueArr.clear()
    for index in range(valueCount):
        optionValueArr.append([carID])
    #
    for typeItem in paramTypeArr :
        for valueItem in optionTempDict["body"]["items"]:
            if typeItem[-2] == "参配看图" or typeItem[-2] == "进气形式" :
                if typeItem[-1] == valueItem["Item"]:
                    optionFieldArr.append("op."+str(typeItem[2]))
                    for i in range(len(valueItem["ModelExcessIds"])):
                        #print("i:" + str(i))
                        optionValueArr[i].append('\"'+str(valueItem["ModelExcessIds"][i]["Value"])+'\"')
                    break
            elif typeItem[-2] == valueItem["Name"]:
                optionFieldArr.append("op."+str(typeItem[2]))
                for i in range(len(valueItem["ModelExcessIds"])):
                    optionValueArr[i].append('\"'+str(valueItem["ModelExcessIds"][i]["Value"])+'\"')
                break
    #print("optionFieldArr---------" + str(optionFieldArr))
    #print("optionValueArr---------" + str(optionValueArr))


#加载html
def loadHtml(car):
    carID = str(car[0])
    url = "https://price.pcauto.com.cn/"+str(car[2])+"/config.html"
    print(carID+"|"+url)

    response = requests.get(url,headers=head)
    soup = BeautifulSoup(response.text, "html.parser")

    pattern = re.compile(r"var config = (.*?);", re.MULTILINE | re.DOTALL)
    script = soup.find("script", text=pattern)
    originConfigData = pattern.search(script.text).group(1)
    configData = originConfigData.replace("curId","\"curId\"")

    optionPattern = re.compile(r"var option = (.*?);", re.MULTILINE | re.DOTALL)
    optionScript = soup.find("script", text=optionPattern)
    originOptionData = optionPattern.search(optionScript.text).group(1)
    optionData = originOptionData.replace("curId","\"curId\"")

    #print(str(optionData))
    if configData != None and optionData != None :
        configDict = json.loads(configData)
        optionDict = json.loads(optionData)
        #print(str(optionDict))
        filterFieldName(configDict,carID)
        filterOptionFieldName(optionDict,carID)
        statusJson = configDict['success'] if 'success' in configDict.keys() else 0
        optionStatusJson = optionDict['success'] if 'success' in optionDict.keys() else 0
        #print(str(valueArr))
        if statusJson == 1 and optionStatusJson == 1 and len(valueArr)>0 and len(valueArr[0])!=1:
            sqlField = "INSERT INTO pcauto_car_param ("+",".join(fieldArr)+") VALUES "
            optionSqlField = "INSERT INTO pcauto_car_param_option ("+",".join(optionFieldArr)+") VALUES "
            #print(sqlField)
            #print(optionSqlField)
            tempArr = []
            for valueItem in valueArr:
                if len(valueItem) == 1 : 
                    continue
                tempArr.append("("+",".join(valueItem)+")")

            sqlStr = sqlField + ",".join(tempArr)
            print(sqlStr)
            optionTempArr = []
            for valueItem in optionValueArr:
                if len(valueItem) == 1 : 
                    continue
                optionTempArr.append("("+",".join(valueItem)+")")

            optionSqlStr = optionSqlField + ",".join(optionTempArr)
            print(optionSqlStr)
            print("param count:"+str(len(tempArr))+"option count:"+str(len(optionTempArr)))
            return
            try:
                # Please write your MySQL's information.
                cur = conn.cursor()
                cur.execute(sqlStr)
                if len(optionTempArr) != 0 :
                    cur.execute(optionSqlStr)
                else:
                    print("无可选配置信息")

                cur.execute("SELECT id,car_id FROM pcauto_car_param WHERE car_id="+carID)
                configIdArr =[]
                for temp in cur.fetchall():
                    configIdArr.append(temp[0])
                #print(configIdArr)

                optionIdArr =[]
                if len(optionTempArr) != 0 :
                    cur.execute("SELECT id,car_id FROM pcauto_car_param_option WHERE car_id="+carID)
                    for temp in cur.fetchall():
                        optionIdArr.append(temp[0])
                #print(optionIdArr)

                count = len(configIdArr) #min(len(configIdArr),len(optionIdArr))
                optionCount = len(optionIdArr)
                idTempArr = []
                for index in range(count):
                    tempOptionId = "0"
                    if index<optionCount : tempOptionId = str(optionIdArr[index])
                    idTempArr.append("("+carID+","+str(configIdArr[index])+","+tempOptionId+")")
                idSqlStr = "INSERT INTO pcauto_car_relation (car_id, param_id, option_param_id) VALUES " + ",".join(idTempArr)
                cur.execute(idSqlStr)
                #print(idSqlStr)

                conn.commit()
            except Exception as e:
                print("sql execute error: " + e)
        else:
            print("数据问题")


if __name__ == "__main__" :
    inquireCar()
    inquireParamType()

    pool = ThreadPool(1)
    try:
        results = pool.map(loadHtml, carArr)
    except Exception as e:
        print(e)

    pool.close()
    pool.join()
    
    
    


# 0_100_speed_up, measured_0_100_speed_up, measured_100_0_braking, measured_fuel_consumption, offical_fuel_consumption, offical_pure_electric_range, quality_assurance, body_image_url, body_type, body_length, body_width, body_height, body_wheelbase, body_front_wheel_pitch, body_rear_wheel_pitch, body_min_ground_clearance, body_weight, body_door_num, body_sit_num, body_tank_volume, body_reserve_compartment_volume, body_reserve_compartment_max_volume, body_reserve_compartment_size, engine_image_url, engine_model, engine_displacement, engine_intake_form, engine_max_ps, engine_max_power, engine_max_power_speed, engine_max_torque, engine_max_torque_speed, engine_cylinder_arrangement_form, engine_cylinder_num, engine_cylinder_door_num, engine_compression_ratio, engine_valve_train, engine_cylinder_diameter, engine_Itinerary, engine_specific_technology, engine_fuel_form, engine_fuel_grade, engine_fuel_supply_mode, engine_cylinder_head_material, engine_cylinder_block_material, engine_emission_standard, gearbox_name, gearbox_gear_num, gearbox_type, turn_image_url, turn_driving_mode, turn_four_wheel_drive, turn_central_differential_structure, turn_front_suspension_type, turn_back_suspension_type, turn_steering_boost_type, turn_car_body_structure, wheelbrake_image_url, wheelbrake_front_brake_type, wheelbrake_back_brake_type, wheelbrake_parking_brake_type, wheelbrake_front_tire_specification, wheelbrake_back_tire_specification, wheelbrake_spare_tire_specification, wheelbrake_spare_tire_size, offroad_approach_angle, offroad_departure_angle, offroad_longitudinal_angle_passage, offroad_climbing_angle, offroad_min_ground_clearance, offroad_min_turning_radius, offroad_max_wading_depth